USE [mydb] --把mydb改成我的数据库名称
GO
sp_dbcmptlevel [mydb], 90 --把mydb改成我的数据库名称
GO
DECLARE @db NVARCHAR(500)
SET @db = '[mydb]' --把mydb改成我的数据库名称

EXEC sp_configure 'show advanced options','1'; 
RECONFIGURE; 
EXEC sp_configure 'clr enabled','1' 
RECONFIGURE; 

DECLARE @sql NVARCHAR(MAX)
SET @sql = N'ALTER DATABASE '+@db+' SET TRUSTWORTHY ON'
exec sp_executesql @sql


DECLARE @CONSTRAINT_NAME VARCHAR(500)
DECLARE @TABLE_NAME VARCHAR(500)
DECLARE @COLUMN_NAME VARCHAR(500)
DECLARE @CONSTRAINT_TABLE TABLE
 (
      CONSTRAINT_NAME VARCHAR(500),
      TABLE_NAME VARCHAR(500),
      COLUMN_NAME VARCHAR(500),
      VALUE INT
 )
 
 

INSERT @CONSTRAINT_TABLE
SELECT
    OBJECT_NAME(t1.referencing_id) AS CONSTRAINT_NAME,
    OBJECT_NAME(t3.parent_object_id) AS TABLE_NAME,
    COL_NAME(t3.parent_object_id, t3.parent_column_id) AS COLUMN_NAME,
    0 AS VALUE
FROM
    sys.sql_expression_dependencies as t1
    left join sys.default_constraints as t3 on t1.referencing_id = t3.object_id
WHERE
    t1.referenced_class = 1 -- 1表示函数
    AND (t1.referenced_entity_name = 'Id_GenerateChar' OR t1.referenced_entity_name = 'Id_GenerateBinary')
    AND t1.is_schema_bound_reference = 1
    

WHILE EXISTS(SELECT CONSTRAINT_NAME FROM @CONSTRAINT_TABLE WHERE VALUE = 0)
	BEGIN
		SELECT TOP 1 @CONSTRAINT_NAME = CONSTRAINT_NAME, @TABLE_NAME = TABLE_NAME, @COLUMN_NAME = COLUMN_NAME FROM @CONSTRAINT_TABLE WHERE VALUE = 0
		EXEC('ALTER TABLE [' + @TABLE_NAME + '] DROP CONSTRAINT  [' + @CONSTRAINT_NAME + ']')
		UPDATE @CONSTRAINT_TABLE SET VALUE = 1 WHERE CONSTRAINT_NAME=@CONSTRAINT_NAME;
	END
	
	

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Id_GenerateChar]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[Id_GenerateChar]
	
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Id_GenerateBinary]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[Id_GenerateBinary]
	
IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'App.Id.Clr.dll' and is_user_defined = 1)
	DROP ASSEMBLY [App.Id.Clr.dll]


declare @user varchar(50)
SELECT  @user = quotename(SL.Name)
  FROM  master..sysdatabases SD inner join master..syslogins SL
    on  SD.SID = SL.SID
 Where  SD.Name = DB_NAME()
exec('exec sp_changedbowner ' + @user)


CREATE ASSEMBLY [App.Id.Clr.dll]
AUTHORIZATION [dbo]
FROM 'C:\bin\App.Id.Clr.dll'
WITH PERMISSION_SET = UNSAFE



EXEC('
CREATE FUNCTION [dbo].[Id_GenerateChar]
(
	@rand FLOAT
)
RETURNS NCHAR(24)
AS
	EXTERNAL NAME [App.Id.Clr.dll].[App.Id].[GenerateChar]')
	

EXEC('
CREATE FUNCTION [dbo].[Id_GenerateBinary]
(
	@rand FLOAT
)
RETURNS BINARY(12)
AS
	EXTERNAL NAME [App.Id.Clr.dll].[App.Id].[GenerateBinary]')


	
WHILE EXISTS(SELECT CONSTRAINT_NAME FROM @CONSTRAINT_TABLE WHERE VALUE = 1)
	BEGIN
		SELECT TOP 1 @CONSTRAINT_NAME = CONSTRAINT_NAME, @TABLE_NAME = TABLE_NAME, @COLUMN_NAME = COLUMN_NAME FROM @CONSTRAINT_TABLE WHERE VALUE = 1
		EXEC('ALTER TABLE [' + @TABLE_NAME + '] ADD CONSTRAINT [' + @CONSTRAINT_NAME + '] DEFAULT (dbo.Id_GenerateChar(CHECKSUM(NEWID()))) FOR [' + @COLUMN_NAME + ']')
		UPDATE @CONSTRAINT_TABLE SET VALUE = 2 WHERE CONSTRAINT_NAME=@CONSTRAINT_NAME;
	END
